# coding:utf-8
# Created by yang

import pymysql
from .. import settings

conn = pymysql.connect(
    host=settings.MYSQL_HOST,
    port=settings.MYSQL_PORT,
    user=settings.MYSQL_USER,
    password=settings.MYSQL_PWD,
    db=settings.MYSQL_DB,
    charset='utf8'
)

cursor = conn.cursor()


class Sql(object):
    @staticmethod
    def insert(table_name, **kwargs):
        keys, vals = tuple(kwargs.keys()), tuple(kwargs.values())
        cols = ",".join(keys)
        wildcards = ",".join(["%s" for i in range(len(vals))])
        sql = 'insert into {}({}) VALUES ({});'.format(table_name, cols, wildcards)
        # print(sql)
        cursor.execute(sql, args=vals)
        conn.commit()

    @staticmethod
    def select(table_name, *cols,limit=None):
        """
        :param table_name: 表名,str
        :param sql: 直接执行的sql语句
        :param limit: 查询行数,int型,None表示查询苏所有
        :param cols: 列名
        :return: 
        """

        def validate_params():
            """参数检查"""
            import re
            if re.findall(r'\W', table_name):
                raise Exception("请传入正确的表名")
            for i in cols:
                if re.findall(r'\W', i):
                    raise Exception("请传入正确的列名")
            if limit:
                if not isinstance(limit, int):
                    raise Exception("limit需传入int型")

        validate_params()

        if not cols and not limit:  # select * ,无limit
            sql = 'select * from {};'.format(table_name)
        elif limit:
            if not cols:  # select * 有limit
                sql = 'select * from {} limit {};'.format(table_name, limit)
            else:  # select x1,x2 有limit
                col_names = ",".join(cols)
                sql = 'select {} from {} limit {};'.format(col_names, table_name, limit)
        else:
            # no limit,有col值
            col_names = ",".join(cols)
            sql = 'select {} from {}'.format(col_names, table_name)

        cursor.execute(sql)
        conn.commit()
        for i in cursor.fetchall():
            print(i)

    @staticmethod
    def is_repeat(table_name, **kwargs):
        x = " and ".join(['{}="{}"'.format(k, v) for k, v in kwargs.items()])
        sql = 'select count(1) from {} where {};'.format(table_name, x)
        cursor.execute(sql)
        res = cursor.fetchone()[0]
        if res >= 1:
            return True


if __name__ == '__main__':
    # Sql.insert("article", title="博客1", href="www.baidu.com")
    # Sql.select("article", "title","url",limit=10)
    # Sql.select("article", "title", "url")
    # res = Sql.is_repeat("article", title='Oldboy s4 Flask',url='http://www.cnblogs.com/wupeiqi/articles/7552008.html')
    res = Sql.is_repeat("article", title='Oldboy s4 Flask')
    print(res)
    Sql.select("article",)
